package com.ldk.base.framework.datasource.generator;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang.StringUtils;

import com.ldk.base.framework.datasource.DataSourceDriver;;


/**
 * MSSQL表实体生成器
 * 
 * @author xiangwl
 * 
 */
public class MSSqlEntityGenerator extends EntityGenerator {

	/**
	 * 构造函数
	 * 
	 * @param serverName
	 * @param portNumber
	 * @param databaseName
	 * @param user
	 * @param password
	 */
	public MSSqlEntityGenerator(String serverName, int portNumber,
			String databaseName, String user, String password) {
		setServerName(serverName);
		setPortNumber(portNumber);
		setDatabaseName(databaseName);
		setUser(user);
		setPassword(password);
	}

	@Override
	protected List<String> getTables() {
		String sql = String.format("SELECT name FROM %s..SysObjects Where xtype='U' ORDER BY name", getDatabaseName());
		
		List<String> tables = new ArrayList<String>();
		try{
			Connection conn = getConnection();
			if(conn != null){
				Statement statement =  conn.createStatement();
				ResultSet rs = statement.executeQuery(sql);
				if(rs != null){
					while (rs.next()) {
						tables.add(rs.getString("name"));
					}
				}
				statement.close();
				conn.close();
			}
		}
		catch(Exception e){
			logger.error(e.getMessage());
		}
		return tables;
	}

	@Override
	protected List<TableColumn> getTableColums(String table) {
		StringBuilder builder = new StringBuilder();
		builder.append(" select a.Name as COLUMN_NAME,(case when a.is_nullable=1 then 1 else 0 end) as IS_NULLABLE,b.Name as DATA_TYPE, ");
		builder.append("		ColumnProperty(a.object_id,a.Name,'Precision') as NUMERIC_PRECISION, ");
		builder.append("		(case when exists(select 1 from sys.objects where parent_object_id=a.object_id and type=N'PK' and name in ");
		builder.append("			 (select Name from sys.indexes where index_id in ");
		builder.append("			 (select indid from sysindexkeys where id = a.object_id and colid=a.column_id))) ");
		builder.append("			 then 1 else 0 end) as COLUMN_KEY, ");
		builder.append("	   (case when is_identity=1 then 1 else 0 end) as EXTRA, ");
		builder.append("	   cast(isnull(e.[value],'') as nvarchar(100)) as COLUMN_COMMENT ");
		builder.append(" from sys.columns a ");
		builder.append(" left join sys.types b on a.user_type_id=b.user_type_id ");
		builder.append(" inner join sys.objects c on a.object_id=c.object_id and c.Type='U' ");
		builder.append(" left join syscomments d on a.default_object_id=d.id ");
		builder.append(" left join sys.extended_properties e on e.major_id=c.object_id and e.minor_id=a.column_id and e.class=1 ");
		builder.append(" left join sys.extended_properties f on f.major_id=c.object_id and f.minor_id=0 and f.class=1 ");
		builder.append(String.format(" where c.name = '%s'", table));
		
		String sql = builder.toString();
		
		List<TableColumn> columns = new ArrayList<TableColumn>();
		try{
			Connection conn = getConnection();
			if(conn != null){
				Statement statement =  conn.createStatement();
				ResultSet rs = statement.executeQuery(sql);
				if(rs != null){
					while (rs.next()) {
						TableColumn column = new TableColumn();
						String columnName = rs.getString("COLUMN_NAME");
						column.setColumnName(columnName);
						String propertyName = underlineToCamel(columnName);
						column.setPropertyName(propertyName);
						String isNUllable = rs.getString("IS_NULLABLE");
						if(!StringUtils.isEmpty(isNUllable) && isNUllable.equalsIgnoreCase("0")){
							column.setIsNullable(false);
						}
						else{
							column.setIsNullable(true);
						}
						String dataType = rs.getString("DATA_TYPE");
						column.setDataType(getColumnDataType(dataType));
						int precision = rs.getInt("NUMERIC_PRECISION");
						column.setPrecision(precision);
						String key = rs.getString("COLUMN_KEY");//PRI
						if(!StringUtils.isEmpty(key) && key.equalsIgnoreCase("1")){
							column.setIsKey(true);
						}
						else{
							column.setIsKey(false);
						}
						String extra = rs.getString("EXTRA");//auto_increment
						if(!StringUtils.isEmpty(extra) && extra.equalsIgnoreCase("1")){
							column.setIsAutoIncrement(true);
						}
						else{
							column.setIsAutoIncrement(false);
						}
						String comment = rs.getString("COLUMN_COMMENT");
						column.setComment(comment);
						//添加
						columns.add(column);
					}
				}
				statement.close();
				conn.close();
			}
		}
		catch(Exception e){
			logger.error(e.getMessage());
		}
		return columns;
	}

	@Override
	public Connection getConnection() {
		//jdbc:sqlserver://114.215.134.45:1433;DatabaseName=gysfw
		String url = String.format(
				"jdbc:sqlserver://%s:%d;DatabaseName=%s",
				getServerName(), getPortNumber(), getDatabaseName());
		Connection conn = null;
		try {
			Class.forName(DataSourceDriver.MSSQL.getNoXADriver());
			conn = DriverManager.getConnection(url, getUser(), getPassword());
		} catch (Exception e) {

		}
		return conn;
	}

	@Override
	protected DBType getDbType() {
		return DBType.MSSQL;
	}

}
